require('dotenv').config();
const mysql = require('mysql2/promise');

const dbConfig = {
  host: process.env.DB_HOST || '43.143.253.188',
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || 'nextjs',
  password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
  database: process.env.DB_NAME || 'nextjs',
  charset: 'utf8mb4'
};

async function testPyNameField() {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功！');

    // 测试查询包含py_name字段的数据
    console.log('\n=== 测试py_name字段查询 ===');
    const [samples] = await connection.execute(`
      SELECT id, name, py_name, star_level 
      FROM deputy_general_base 
      WHERE py_name IS NOT NULL 
      ORDER BY id 
      LIMIT 10
    `);

    console.log('副将基础数据（包含py_name字段）:');
    samples.forEach(row => {
      console.log(`[${row.id}] ${row.name} -> ${row.py_name} (${row.star_level}星)`);
    });

    // 测试联表查询（模拟NestJS服务中的查询）
    console.log('\n=== 测试联表查询（模拟NestJS服务） ===');
    const [joinedSamples] = await connection.execute(`
      SELECT 
        d.id,
        d.name,
        d.level,
        d.role_type,
        d.star,
        b.py_name,
        b.star_level,
        pd.pos
      FROM deputy_general d
      LEFT JOIN deputy_general_base b ON d.base_id = b.id
      LEFT JOIN player_deputy pd ON d.id = pd.deputy_id
      WHERE b.py_name IS NOT NULL
      ORDER BY d.id
      LIMIT 5
    `);

    console.log('联表查询结果（模拟NestJS服务返回格式）:');
    joinedSamples.forEach(row => {
      console.log(`[${row.id}] ${row.name} (${row.py_name}) - 等级:${row.level}, 星级:${row.star_level}, 位置:${row.pos || 0}`);
    });

    // 验证字段类型和约束
    console.log('\n=== 验证字段定义 ===');
    const [fieldInfo] = await connection.execute(`
      SELECT 
        COLUMN_NAME,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        IS_NULLABLE,
        COLUMN_DEFAULT,
        COLUMN_COMMENT
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'deputy_general_base' AND COLUMN_NAME = 'py_name'
    `, [dbConfig.database]);

    if (fieldInfo.length > 0) {
      const field = fieldInfo[0];
      console.log('py_name字段定义:');
      console.log(`  字段名: ${field.COLUMN_NAME}`);
      console.log(`  数据类型: ${field.DATA_TYPE}`);
      console.log(`  最大长度: ${field.CHARACTER_MAXIMUM_LENGTH}`);
      console.log(`  可空: ${field.IS_NULLABLE}`);
      console.log(`  默认值: ${field.COLUMN_DEFAULT || 'NULL'}`);
      console.log(`  注释: ${field.COLUMN_COMMENT || '无'}`);
    }

  } catch (error) {
    console.error('测试过程中发生错误:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n数据库连接已关闭。');
    }
  }
}

testPyNameField().catch(console.error);
